package indi.mozping.hbase.tophoenix;


import indi.mozping.entity.Event;

import java.sql.*;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.List;
import java.util.Random;
import java.util.concurrent.atomic.AtomicInteger;


/**
 * @author by mozping
 * @Classname ReadWriteDeputyFace
 * @Description TODO
 * @Date 2020/4/7 17:27
 */
public class ReadWriteEvent {
    private static final String str = "yyyy-MM-dd HH:mm:ss";

    private static final Random random = new Random();
    private static final String randomStr = "1234567890";
    private static Connection connection = null;
    private static PreparedStatement prepareStatement = null;
    private static PreparedStatement readprepareStatement = null;


//    private static final String sql = "upsert into event_nofeature_noindex (aid,sys_code,thumbnail_id,thumbnail_url,image_id,\n" +
//            "image_url,feature_info,algo_version,gender_info,age_info,hairstyle_info,hat_info,glasses_info,race_info,mask_info,skin_info,pose_info" +
//            ",quality_info,target_rect,target_rect_float,land_mark_info,source_id,source_type" +
//            ",site,feature_quality,time,create_time,save_time,score" +
//            ",column1,column2,column3,target_thumbnail_rect,field1) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

    private static final String sql = "upsert into bigdata_dim.dim_bigdata_event_face_person_5030   values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

//    private static final String read_sql =
//            "select * from bigdata_dim.dim_bigdata_event_face_person_5030   where dt = ? And thumbnail_id  >=  ? order by  thumbnail_id  limit ?";

    private static final String read_sql =
            "select * from bigdata_dim.dim_bigdata_event_face_person_5030   where dt = ? And thumbnail_id  >=  ?  limit ?";

    static {
        try {
            Class.forName("org.apache.phoenix.jdbc.PhoenixDriver");
            connection = DriverManager.getConnection(Config.CONN_URL);
            prepareStatement = connection.prepareStatement(sql);
            readprepareStatement = connection.prepareStatement(read_sql);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) throws SQLException {

        readEvent("2020-04-16", "10", 10);
        writeEvent();
    }


    public static boolean readEvent(String dt, String begin, int limit) throws SQLException {
        readprepareStatement.setString(1, dt);
        readprepareStatement.setString(2, begin);
        readprepareStatement.setInt(3, limit);
        long beginTime = System.currentTimeMillis();
        ResultSet rs = readprepareStatement.executeQuery();
        int count = 0;
        while (rs.next()) {
            count++;
        }
        System.out.println((System.currentTimeMillis() - beginTime) + " ms");
        if (count != limit) {
            System.out.println("Query from " + dt + " begin with " + begin + " limit " + limit + " is fail or end ! count is: " + count);
            return false;
        } else {
            System.out.println("Query from " + dt + " begin with " + begin + " limit " + limit + " is ok ! ");
            return true;
        }
        //connection.close();
    }


    public static void writeEvent() {

        try {
            prepareStatement.setString(1, "1");
            prepareStatement.setString(2, "1");
            prepareStatement.setString(3, "1");
            prepareStatement.setString(4, "1");
            prepareStatement.setString(5, "1");
            prepareStatement.setString(6, "1");

            prepareStatement.setBytes(7, "1".getBytes());

            prepareStatement.setString(8, "1");
            prepareStatement.setString(9, "1");
            prepareStatement.setString(10, "1");
            prepareStatement.setString(11, "1");
            prepareStatement.setString(12, "1");
            prepareStatement.setString(13, "1");
            prepareStatement.setString(14, "1");
            prepareStatement.setString(15, "1");
            prepareStatement.setString(16, "1");
            prepareStatement.setString(17, "1");

            prepareStatement.setFloat(18, 2.0F);

            prepareStatement.setString(19, "2");
            prepareStatement.setString(20, "2");
            prepareStatement.setString(21, "2");
            prepareStatement.setString(22, "2");
            prepareStatement.setString(23, "2");
            prepareStatement.setString(24, "2");

            prepareStatement.setFloat(25, 2.0F);

            SimpleDateFormat sdf = new SimpleDateFormat(str);
            String dateStr = sdf.format(new java.util.Date());
            prepareStatement.setString(26, dateStr.split(" ")[0]);

            prepareStatement.setDate(27, new Date(System.currentTimeMillis()));
            prepareStatement.setDate(28, new Date(System.currentTimeMillis()));
            prepareStatement.setDate(29, new Date(System.currentTimeMillis()));

            prepareStatement.setString(30, "2");
            prepareStatement.setString(31, "2");
            prepareStatement.setString(32, "2");
            prepareStatement.setString(33, "2");
            prepareStatement.setString(34, "2");
            prepareStatement.setString(35, "2");

            int i = prepareStatement.executeUpdate();
            System.out.println(i + " --> " + i);
            connection.commit();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                connection.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

    public static void writeEvent(Event event) throws SQLException {

        prepareStatement.setString(1, event.getThumbnail_id());
        prepareStatement.setString(2, event.getAid());
        prepareStatement.setString(3, event.getSys_code());
        prepareStatement.setString(4, event.getThumbnail_url());
        prepareStatement.setString(5, event.getImage_id());
        prepareStatement.setString(6, event.getImage_url());

        prepareStatement.setBytes(7, event.getFeature_info());

        prepareStatement.setString(8, event.getAlgo_version());
        prepareStatement.setString(9, event.getGender_info());
        prepareStatement.setString(10, event.getAge_info());
        prepareStatement.setString(11, event.getHairstyle_info());
        prepareStatement.setString(12, event.getHat_info());
        prepareStatement.setString(13, event.getGlasses_info());
        prepareStatement.setString(14, event.getRace_info());
        prepareStatement.setString(15, event.getMask_info());
        prepareStatement.setString(16, event.getSkin_info());
        prepareStatement.setString(17, event.getPose_info());

        prepareStatement.setFloat(18, event.getQuality_info());

        prepareStatement.setString(19, event.getTarget_rect());
        prepareStatement.setString(20, event.getTarget_rect_float());
        prepareStatement.setString(21, event.getLand_mark_info());
        prepareStatement.setString(22, event.getSource_id());
        prepareStatement.setString(23, event.getSource_type());
        prepareStatement.setString(24, event.getSite());

        prepareStatement.setFloat(25, event.getFeature_quality());

        SimpleDateFormat sdf = new SimpleDateFormat(str);
        String dateStr = sdf.format(event.getCreate_time());
        prepareStatement.setString(26, dateStr.split(" ")[0]);

        prepareStatement.setDate(27, new Date(event.getTime().getTime()));
        prepareStatement.setDate(28, new Date(event.getCreate_time().getTime()));
        prepareStatement.setDate(29, new Date(event.getSave_time().getTime()));

        prepareStatement.setString(30, event.getScore());
        prepareStatement.setString(31, event.getColumn1());
        prepareStatement.setString(32, event.getColumn2());
        prepareStatement.setString(33, event.getColumn3());
        prepareStatement.setString(34, event.getTarget_thumbnail_rect());
        prepareStatement.setString(35, event.getField1());
        connection.commit();
    }


    private static String begin = "";
    private static AtomicInteger beginNum = new AtomicInteger(1);
    private static DecimalFormat df = new DecimalFormat("000000");

    public static void writeEventRandom(List<Event> eventList) throws SQLException {
        for (Event event : eventList) {
            //prepareStatement.setString(1, Long.toString(Math.abs(random.nextLong())));
            //prepareStatement.setString(1, event.getThumbnail_id());
            prepareStatement.setString(1, getRandom(16));
            //prepareStatement.setString(1, begin + (beginNum.getAndIncrement()));


            String str = df.format(beginNum.getAndIncrement());
            //prepareStatement.setString(1, str);

            prepareStatement.setString(2, event.getAid());
            prepareStatement.setString(3, event.getSys_code());
            prepareStatement.setString(4, event.getThumbnail_url());
            prepareStatement.setString(5, event.getImage_id());
            prepareStatement.setString(6, event.getImage_url());

            prepareStatement.setBytes(7, event.getFeature_info());

            prepareStatement.setString(8, event.getAlgo_version());
            prepareStatement.setString(9, event.getGender_info());
            prepareStatement.setString(10, event.getAge_info());
            prepareStatement.setString(11, event.getHairstyle_info());
            prepareStatement.setString(12, event.getHat_info());
            prepareStatement.setString(13, event.getGlasses_info());
            prepareStatement.setString(14, event.getRace_info());
            prepareStatement.setString(15, event.getMask_info());
            prepareStatement.setString(16, event.getSkin_info());
            prepareStatement.setString(17, event.getPose_info());

            prepareStatement.setFloat(18, event.getQuality_info());

            prepareStatement.setString(19, event.getTarget_rect());
            prepareStatement.setString(20, event.getTarget_rect_float());
            prepareStatement.setString(21, event.getLand_mark_info());
            prepareStatement.setString(22, event.getSource_id());
            prepareStatement.setString(23, event.getSource_type());
            prepareStatement.setString(24, event.getSite());

            prepareStatement.setFloat(25, event.getFeature_quality());

            SimpleDateFormat sdf = new SimpleDateFormat(str);
            String dateStr = sdf.format(event.getCreate_time());
            prepareStatement.setString(26, dateStr.split(" ")[0]);
            //prepareStatement.setString(26, "2020-04-16");

            prepareStatement.setDate(27, new Date(event.getTime().getTime()));
            prepareStatement.setDate(28, new Date(event.getCreate_time().getTime()));
            prepareStatement.setDate(29, new Date(event.getSave_time().getTime()));

            prepareStatement.setString(30, event.getScore());
            prepareStatement.setString(31, event.getColumn1());
            prepareStatement.setString(32, event.getColumn2());
            prepareStatement.setString(33, event.getColumn3());
            prepareStatement.setString(34, event.getTarget_thumbnail_rect());
            prepareStatement.setString(35, event.getField1());


            prepareStatement.executeUpdate();
        }
        connection.commit();
    }

    static String getRandom(int num) {
        StringBuilder sb = new StringBuilder();
        int index = Math.abs(random.nextInt()) % 9;
        for (int i = 0; i < num; i++) {
            sb.append(randomStr.charAt(index));
            index = Math.abs(random.nextInt()) % 10;
        }
        return sb.toString();
    }

    public static void writeEvent(List<Event> eventList) throws SQLException {
        for (Event event : eventList) {
            prepareStatement.setString(1, event.getThumbnail_id());
            prepareStatement.setString(2, event.getAid());
            prepareStatement.setString(3, event.getSys_code());
            prepareStatement.setString(4, event.getThumbnail_url());
            prepareStatement.setString(5, event.getImage_id());
            prepareStatement.setString(6, event.getImage_url());

            prepareStatement.setBytes(7, event.getFeature_info());

            prepareStatement.setString(8, event.getAlgo_version());
            prepareStatement.setString(9, event.getGender_info());
            prepareStatement.setString(10, event.getAge_info());
            prepareStatement.setString(11, event.getHairstyle_info());
            prepareStatement.setString(12, event.getHat_info());
            prepareStatement.setString(13, event.getGlasses_info());
            prepareStatement.setString(14, event.getRace_info());
            prepareStatement.setString(15, event.getMask_info());
            prepareStatement.setString(16, event.getSkin_info());
            prepareStatement.setString(17, event.getPose_info());

            prepareStatement.setFloat(18, event.getQuality_info());

            prepareStatement.setString(19, event.getTarget_rect());
            prepareStatement.setString(20, event.getTarget_rect_float());
            prepareStatement.setString(21, event.getLand_mark_info());
            prepareStatement.setString(22, event.getSource_id());
            prepareStatement.setString(23, event.getSource_type());
            prepareStatement.setString(24, event.getSite());

            prepareStatement.setFloat(25, event.getFeature_quality());

            SimpleDateFormat sdf = new SimpleDateFormat(str);
            String dateStr = sdf.format(event.getCreate_time());
            prepareStatement.setString(26, dateStr.split(" ")[0]);

            prepareStatement.setDate(27, new Date(event.getTime().getTime()));
            prepareStatement.setDate(28, new Date(event.getCreate_time().getTime()));
            prepareStatement.setDate(29, new Date(event.getSave_time().getTime()));

            prepareStatement.setString(30, event.getScore());
            prepareStatement.setString(31, event.getColumn1());
            prepareStatement.setString(32, event.getColumn2());
            prepareStatement.setString(33, event.getColumn3());
            prepareStatement.setString(34, event.getTarget_thumbnail_rect());
            prepareStatement.setString(35, event.getField1());

            prepareStatement.executeUpdate();
        }
        connection.commit();
    }

}
